﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ProjectSheduleAnalyst
{
    internal class DB_Access
    {
        public SqlConnection conn;

        public DB_Access()
        {
            conn = DB_Connection.GetConnection();
        }

        // FillGrid - для отображения таблицы БД в DataGridView
        public DataSet FillGrid(string Query, string Table)
        {
            DataSet ds = new DataSet();
            try
            {
                if (conn.State.ToString() == "Closed")
                {
                    conn.Open();
                }
                SqlCommand newCmd = conn.CreateCommand();
                newCmd.Connection = conn;
                newCmd.CommandType = CommandType.Text;
                newCmd.CommandText = Query;

                SqlDataAdapter da = new SqlDataAdapter(newCmd);

                da.Fill(ds, Table);
                conn.Close();
            }
            catch
            {
            }
            return ds;
        }

        public void MySQLquery(string Command_Text)
        {
            SqlConnection myConnection = conn;
            myConnection.Open();
            SqlCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = Command_Text;
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }

        public DataTable FillClassType()
        {
            DataTable tbl = new DataTable();

            tbl.Columns.Add("ID", typeof(Guid));
            tbl.Columns.Add("ClassType", typeof(string));

            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "select ID, Name from ClassRoomTypes";
            SqlDataReader dr = newCmd.ExecuteReader();
            while (dr.Read())
            {
                tbl.Rows.Add(dr["ID"], dr["Name"]);
       
            }
            conn.Close();
            return tbl;
        }


        public DataTable FillLessonsTable()
        {
            DataTable tbl = new DataTable();
            if (conn.State.ToString() == "Closed")
            {
                conn.Open();
            }
            SqlCommand newCmd = conn.CreateCommand();
            newCmd.Connection = conn;
            newCmd.CommandType = CommandType.Text;
            newCmd.CommandText = "SelectLessonsInformation";

            SqlDataAdapter da = new SqlDataAdapter(newCmd);
            da.Fill(tbl);

            conn.Close();
            return tbl;
        }
    }
}
